<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="ca20941">CREATE RULE</title><body><p id="sql_command_desc">Defines a new rewrite rule.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">CREATE [OR REPLACE] RULE <varname>name</varname> AS ON <varname>event</varname>
  TO <varname>table_name</varname> [WHERE <varname>condition</varname>] 
  DO [ALSO | INSTEAD] { NOTHING | <varname>command</varname> | (<varname>command</varname>; <varname>command</varname> 
  ...) }</codeblock></section><section id="section3"><title>Description</title><p><codeph>CREATE RULE</codeph> defines a new rule applying to a specified
table or view. <codeph>CREATE OR REPLACE RULE</codeph> will either create
a new rule, or replace an existing rule of the same name for the same
table. </p><p>The Greenplum Database rule system allows one to define an alternate action to be performed on
        insertions, updates, or deletions in database tables. A rule causes additional or alternate
        commands to be run when a given command on a given table is run. An
          <codeph>INSTEAD</codeph> rule can replace a given command by another, or cause a command
        to not be run at all. Rules can be used to implement SQL views as well. It is important
        to realize that a rule is really a command transformation mechanism, or command macro. The
        transformation happens before the execution of the command starts. It does not operate
        independently for each physical row as does a trigger.</p><p><codeph>ON SELECT</codeph> rules must be unconditional <codeph>INSTEAD</codeph> rules and must
        have actions that consist of a single <codeph>SELECT</codeph> command. Thus, an <codeph>ON
          SELECT</codeph> rule effectively turns the table into a view, whose visible contents are
        the rows returned by the rule's <codeph>SELECT</codeph> command rather than whatever had
        been stored in the table (if anything). It is considered better style to write a
          <codeph>CREATE VIEW</codeph> command than to create a real table and define an <codeph>ON
          SELECT</codeph> rule for it.  </p><p>You can create the illusion of an updatable view by defining <codeph>ON INSERT</codeph>,
          <codeph>ON UPDATE</codeph>, and <codeph>ON DELETE</codeph> rules to replace update actions
        on the view with appropriate updates on other tables. If you want to support <codeph>INSERT
          RETURNING</codeph> and so on, be sure to put a suitable <codeph>RETURNING</codeph> clause
        into each of these rules.</p><p>There is a catch if you try to use conditional rules for view updates:
there must be an unconditional <codeph>INSTEAD</codeph> rule for each
action you wish to allow on the view. If the rule is conditional, or
is not <codeph>INSTEAD</codeph>, then the system will still reject attempts
to perform the update action, because it thinks it might end up trying
to perform the action on the dummy table of the view in some cases. If
you want to handle all the useful cases in conditional rules, add an
unconditional <codeph>DO INSTEAD NOTHING</codeph> rule to ensure that
the system understands it will never be called on to update the dummy
table. Then make the conditional rules non-<codeph>INSTEAD</codeph>;
in the cases where they are applied, they add to the default <codeph>INSTEAD
NOTHING</codeph> action. (This method does not currently work to support
<codeph>RETURNING</codeph> queries, however.)</p>
      <note>
        <p> A view that is simple enough to be automatically updatable (see <codeph><xref
              href="CREATE_VIEW.xml#topic1">CREATE VIEW</xref></codeph>) does not require a
          user-created rule in order to be updatable. While you can create an explicit rule anyway,
          the automatic update transformation will generally outperform an explicit rule. </p>
      </note></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name of a rule to create. This must be distinct from the name
of any other rule for the same table. Multiple rules on the same table
and same event type are applied in alphabetical name order.</pd></plentry><plentry><pt><varname>event</varname></pt><pd>The event is one of <codeph>SELECT</codeph>, <codeph>INSERT</codeph>,
<codeph>UPDATE</codeph>, or <codeph>DELETE</codeph>. </pd></plentry><plentry><pt><varname>table_name</varname></pt><pd>The name (optionally schema-qualified) of the table or view the rule
applies to. </pd></plentry><plentry><pt><varname>condition</varname></pt><pd>Any SQL conditional expression (returning boolean). The condition
expression may not refer to any tables except <codeph>NEW</codeph> and
<codeph>OLD</codeph>, and may not contain aggregate functions. <codeph>NEW</codeph>
and <codeph>OLD</codeph> refer to values in the referenced table. <codeph>NEW</codeph>
is valid in <codeph>ON INSERT</codeph> and <codeph>ON UPDATE</codeph>
rules to refer to the new row being inserted or updated. <codeph>OLD</codeph>
is valid in <codeph>ON UPDATE</codeph> and <codeph>ON DELETE</codeph>
rules to refer to the existing row being updated or deleted.</pd></plentry><plentry><pt>INSTEAD</pt><pd><codeph>INSTEAD NOTHING</codeph> indicates that the commands should be run instead of the
            original command. </pd></plentry><plentry><pt>ALSO</pt><pd><codeph>ALSO</codeph> indicates that the commands should be run
in addition to the original command. If neither <codeph>ALSO</codeph>
nor <codeph>INSTEAD</codeph> is specified, <codeph>ALSO</codeph> is the
default. </pd></plentry><plentry><pt><varname>command</varname></pt><pd>The command or commands that make up the rule action. Valid commands
are <codeph>SELECT</codeph>, <codeph>INSERT</codeph>, <codeph>UPDATE</codeph>,
or <codeph>DELETE</codeph>. The special table names <codeph>NEW</codeph>
and <codeph>OLD</codeph> may be used to refer to values in the referenced
table. <codeph>NEW</codeph> is valid in <codeph>ON INSERT</codeph> and
<codeph>ON</codeph><codeph>UPDATE</codeph> rules to refer to the new
row being inserted or updated. <codeph>OLD</codeph> is valid in <codeph>ON
UPDATE</codeph> and <codeph>ON DELETE</codeph> rules to refer to the
existing row being updated or deleted.</pd></plentry></parml></section><section id="section5"><title>Notes</title><p>You must be the owner of a table to create or change rules for it.</p><p>It is very important to take care to avoid circular rules. Recursive
rules are not validated at rule create time, but will report an error
at execution time.</p></section><section id="section6"><title>Examples</title><p>Create a rule that inserts rows into the child table <codeph>b2001</codeph> when a user tries to
        insert into the partitioned parent table <codeph>rank</codeph>:</p><codeblock>CREATE RULE b2001 AS ON INSERT TO rank WHERE gender='M' and 
year='2001' DO INSTEAD INSERT INTO b2001 VALUES (NEW.id, 
NEW.rank, NEW.year, NEW.gender, NEW.count);</codeblock></section><section id="section7"><title>Compatibility</title><p><codeph>CREATE RULE</codeph> is a Greenplum Database language extension,
as is the entire query rewrite system.</p></section><section id="section8"><title>See Also</title><p><codeph><xref href="ALTER_RULE.xml#topic1"/></codeph><codeph><xref href="./DROP_RULE.xml#topic1"
            type="topic" format="dita"/></codeph>, <codeph><xref href="./CREATE_TABLE.xml#topic1"
            type="topic" format="dita"/></codeph>, <codeph><xref href="./CREATE_VIEW.xml#topic1"
            type="topic" format="dita"/></codeph></p></section></body></topic>
